﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Cn.Edwon.Data;
using JEdwonTune.Lib.Hibernate;
using System.Data;
using System.Text.RegularExpressions;

namespace JEdwonTune.Lib {
    public class DbHandler {
        #region 属性
        private DbServerType _DbService = DbServerType.Oracle10g;
        public DbServerType DbService {
            get { return _DbService; }
            set { _DbService = value; }
        }
        private String _Addr = "localhost";
        public String Addr {
            get { return _Addr; }
            set { _Addr = value; }
        }
        private Int32 _Port = 1521;
        public Int32 Port {
            get { return _Port; }
            set { _Port = value; }
        }
        private String _User = "";
        public String User {
            get { return _User; }
            set { _User = value; }
        }
        private String _Pwd = "";
        public String Pwd {
            get { return _Pwd; }
            set { _Pwd = value; }
        }
        private String _SourceName = "";
        public String SourceName {
            get { return _SourceName; }
            set { _SourceName = value; }
        }

        private String _ErrMessage = "";
        public String ErrMessage {
            get { return _ErrMessage; }
        }

        public ICommonProcess Process {
            get {
                switch (_DbService) {
                    case DbServerType.Oracle10g:
                        return new ADO4Oracle(OracleConnectionString);
                    case DbServerType.MySql6:
                        return new ADO4MySql(MySqlConnectionString);
                    case DbServerType.Sqlite3:
                        return new ADO4Sqlite3(Sqlite3ConnectionString);
                    default:
                        return null;
                }
            }
        }
        #endregion

        #region 数据库链接串
        public String MySqlConnectionString {
            get {
                return "server = "+_Addr
                    +"; user = "+_User
                    + "; database = information_schema"
                    +"; port = "+_Port
                    +"; password = "+_Pwd
                    +"; character set = utf8;";
            }
        }
        public String OracleConnectionString {
            get {
                return "Data Source="
                    + "(DESCRIPTION="
                        +"(ADDRESS_LIST="
                            + "(ADDRESS="
                                + "(PROTOCOL=TCP)"
                                + "(HOST=" + _Addr + ")"
                                + "(PORT=" + _Port + ")"
                            + ")"
                        + ")"
                        + "(CONNECT_DATA="
                            + "(SERVER=DEDICATED)"
                            + "(SERVICE_NAME=" + _SourceName + ")"
                        + ")"
                    + ");"
                    + "User Id=" + _User + ";"
                    + "Password=" + _Pwd;
            }
        }
        public String SqlserverConnectionString {
            get { 
                return "Server="+_Addr+";Database="+_SourceName+";User ID="+_User+";Password="+_Pwd+";Trusted_Connection=False";
            }
        }
        public String Sqlite3ConnectionString {
            get {
                if (String.IsNullOrEmpty(_Pwd)) {
                    return "Data Source=" + _SourceName + ";Pooling=true;FailIfMissing=false;";
                } else {
                    return "Data Source=" + _SourceName + ";Pooling=true;FailIfMissing=false;Password=" + _Pwd;
                }
            }
        }
        #endregion


        /// <summary>
        /// 获取用户表或视图
        /// </summary>
        /// <returns></returns>
        public IList<TableEO> LoadTables() {
            DataSet ds = null;
            String sql = "";
            if(_DbService==DbServerType.MySql6){
                sql = "SELECT "
                    + " TABLE_NAME as tName "
                    + ",TABLE_SCHEMA as tSchema"
                    + ",TABLE_COMMENT as tComment"
                    + ",TABLE_TYPE as tType"
                    + " FROM information_schema.TABLES"
                    + " WHERE TABLE_SCHEMA='"+_SourceName+"'"
                    + " ORDER BY TABLE_TYPE,TABLE_NAME";
                ds = Process.CommonSelectAction(sql, "tables");
            } else if (_DbService == DbServerType.Oracle10g) {
                sql = "SELECT "
                   + " tab.TABLE_NAME as tName "
                   + ",'' as tSchema"
                   + ",com.COMMENTS as tComment"
                   + ",'Table' as tType"
                   + " FROM USER_TABLES tab,USER_TAB_COMMENTS com"
                   + " WHERE tab.TABLE_NAME=com.TABLE_NAME"
                   + " ORDER BY tab.TABLE_NAME";

                String sqlv = "SELECT "
                   + " v.VIEW_NAME as tName "
                   + ",'' as tSchema"
                   + ",c.comments as tComment"
                   + ",'View' as tType"
                   + " FROM USER_VIEWS v,USER_TAB_COMMENTS c "
                   + " WHERE v.view_name=c.table_name "
                   + " ORDER BY v.VIEW_NAME";
                DataSet ds2 = Process.CommonSelectAction(sqlv, "views");
                ds = Process.CommonSelectAction(sql, "tables");
                ds.Tables.Add(ds2.Tables[0].Copy());
            } else if (_DbService == DbServerType.Sqlite3) {
                sql = "SELECT "
                   + " tab.NAME as tName "
                   + ",'' as tSchema"
                   + ",'' as tComment"
                   + ",tab.type as tType"
                   + " FROM sqlite_master tab"
                   + " WHERE tab.type='table' or tab.type='view'"
                   + " ORDER BY tab.type,tab.NAME";
                ds = Process.CommonSelectAction(sql, "tables");
            } else if (_DbService == DbServerType.SqlServer) {
            }
            IList<TableEO> list = new List<TableEO>();
            if (ds != null) {
                for (int i = 0; i < ds.Tables.Count;i++ ) {
                    DataTable table = ds.Tables[i];
                    foreach (DataRow row in table.Rows) {
                        TableEO eo = new TableEO();
                        eo.Comment = row["tComment"].ToString();
                        eo.Name = row["tName"].ToString();
                        eo.Schema = row["tSchema"].ToString();
                        eo.Type = row["tType"].ToString();
                        list.Add(eo);
                    }
                }
            }
            return list;
        }
        /// <summary>
        /// 获取列
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public IList<ColumnEO> LoadColumns(String table) {
            String sql = "";
            IList<ColumnEO> list = new List<ColumnEO>();
            if (_DbService == DbServerType.Sqlite3) {
                sql = "SELECT "
                    + " sql "
                    + " FROM sqlite_master "
                    + " where name='" + table + "'";
                DataSet dsSql = Process.CommonSelectAction(sql, "tab");
                if (dsSql != null && dsSql.Tables.Count > 0) {
                    String sqlstr = dsSql.Tables[0].Rows[0]["sql"].ToString();
                    sqlstr = sqlstr.Replace("\r\n", "");
                    sqlstr = sqlstr.Replace("\n", "");
                    sqlstr = sqlstr.Replace("\r", "");
                    sqlstr = sqlstr.Trim();
                    sqlstr = Regex.Replace(sqlstr, "^([^\\(]*\\()", "");
                    String[] items = sqlstr.Split(',');
                    for (int i = 0; i < items.Length; i++) {
                        Match m = Regex.Match(items[i],"\\[([^\\]]*)\\]\\s([^ ]*)");
                        if(m!=null && m.Groups.Count==3){
                            ColumnEO eo = new ColumnEO();
                            eo.Name = m.Groups[1].Value;
                            String t = m.Groups[2].Value;
                            if (t.IndexOf("(") != -1) {
                                eo.DataType = t.Substring(0, t.IndexOf("("));
                                String len = t.Replace(eo.DataType, "").Replace("(", "").Replace(")", "");
                                eo.Length = Convert.ToInt32(len);
                            } else {
                                eo.DataType = t;
                                eo.Length = 0;
                            }
                            eo.Nullable = (items[i].ToLower().IndexOf("not null") == -1);
                            eo.TableName = table;
                            eo.Comment = "";
                            list.Add(eo);
                        }
                    }
                }
            } else{
                if (_DbService == DbServerType.MySql6) {
                    sql = "SELECT "
                        + " COLUMN_NAME as tName "
                        + ",IS_NULLABLE as tNullable"
                        + ",DATA_TYPE as tDataType"
                        + ",COLUMN_COMMENT as tComment"
                        + ",CHARACTER_MAXIMUM_LENGTH as tLength"
                        + " FROM information_schema.COLUMNS"
                        + " WHERE TABLE_SCHEMA='" + _SourceName + "'"
                        + " AND TABLE_NAME='" + table + "'"
                        + " ORDER BY COLUMN_NAME";
                } else if (_DbService == DbServerType.Oracle10g) {
                    sql = "SELECT "
                        + " col.COLUMN_NAME as tName "
                        + ",col.NULLABLE as tNullable"
                        + ",col.DATA_TYPE as tDataType"
                        + ",com.COMMENTS as tComment"
                        + ",col.DATA_LENGTH as tLength"
                        + " FROM USER_TAB_COLUMNS col,USER_COL_COMMENTS com "
                        + " WHERE col.TABLE_NAME=com.TABLE_NAME "
                        + " AND col.COLUMN_NAME=com.COLUMN_NAME "
                        + " AND col.TABLE_NAME='" + table + "'"
                        + " ORDER BY col.COLUMN_NAME";
                } else if (_DbService == DbServerType.SqlServer) {
                }
                DataSet ds = Process.CommonSelectAction(sql, "columns");
                if (ds != null && ds.Tables.Count > 0) {
                    foreach (DataRowView row in ds.Tables[0].DefaultView) {
                        ColumnEO eo = new ColumnEO();
                        eo.Comment = row["tComment"].ToString();
                        eo.Name = row["tName"].ToString();
                        eo.TableName = table;
                        eo.Nullable = (row["tNullable"].ToString() == "YES"
                            || row["tNullable"].ToString() == "Y");
                        eo.DataType = row["tDataType"].ToString();
                        try {
                            eo.Length = Convert.ToInt32(row["tLength"].ToString());
                        } catch {
                            eo.Length = 0;
                        }
                        list.Add(eo);
                    }
                }
            }
            return list;
        }

        /// <summary>
        /// 获取主键
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public String LoadPKs(String table) {
            String sql = "";
            if (_DbService == DbServerType.MySql6) {
                sql = "SELECT "
                    + " col.COLUMN_NAME as tName "
                    + " FROM information_schema.COLUMNS col "
                    + " WHERE col.TABLE_NAME='" + table + "'"
                    + " AND col.COLUMN_KEY='PKI'";
            } else if (_DbService == DbServerType.Oracle10g) {
                sql = "select ucc.column_name as tName,uc.constraint_name as tConstraint"
                    + " from user_constraints uc ,user_cons_columns ucc"
                    + " where  uc.table_name=ucc.table_name"
                    + " and uc.constraint_name=ucc.constraint_name"
                    + " and uc.table_name='"+table+"'  "
                    + " and uc.constraint_type='P'";
            } else if (_DbService == DbServerType.SqlServer) {
                
            }
            DataSet ds = Process.CommonSelectAction(sql, "pks");
            if (ds != null && ds.Tables[0].Rows.Count>0) {
                return ds.Tables[0].Rows[0]["tName"].ToString();
            }
            return null;
        }

        public Boolean TestConnection() {
            String sql = "";
            if (_DbService == DbServerType.Sqlite3) {
                sql = "SELECT NAME FROM sqlite_master LIMIT 0,1";
            } else if (_DbService == DbServerType.MySql6) {
                sql = "SELECT TABLE_NAME FROM information_schema.TABLES LIMIT 0,1";
            } else if (_DbService == DbServerType.Oracle10g) {
                sql = "SELECT TABLE_NAME FROM USER_TABLES WHERE rownum=1";
            } else if (_DbService == DbServerType.SqlServer) {
            }
            try {
                DataSet ds = Process.CommonSelectAction(sql, "tables");
                return (ds != null);
            } catch(Exception ex) {
                _ErrMessage = "TestConnection:"+ex.Message;
                return false;
            }
        }

    }
}
